﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kugar.Core.BaseStruct;
using Kugar.Core.Collections;
using Kugar.Core.Data.MSSQLSqliteHelper;
using System.Data.SqlClient;
using Kugar.Core.ExtMethod;

namespace Kugar.Core.Provider.TreeDataProvider
{
    public class Tree_MSDataBaseDataProvider : ITree_DataProvider
    {
        private SQLHelperPool _pool = null;
        private string _moduleID = "";

        public Tree_MSDataBaseDataProvider(SQLHelperPool  pool, string moduleID)
        {
            _pool = pool;
            _moduleID = moduleID;
        }

        public bool AddCheck(TreeNodeForStorage item)
        {
            string sql = @"
                                    if EXISTS (select top 1 [CategoryID] from Kugar_Category where [CategoryID]='{0}')
                                        begin
                                                select -1
                                        end 
                                    else
                                        if '{1}' <> ''
                                        begin
                                            if EXISTS(select top 1 [CategoryID] from Kugar_Category where [CategoryID]='{1}')
                                                begin
                                                        select -2
                                                 end 
                                        end
                                    
                                     select 0
                                ";

            using (var conn=_pool.Take())
            {
                int ret = 0;

                ret = conn.ExecuteScalar<int>(string.Format(sql, item.Key.ToStringEx(), item.ParentKey.ToStringEx()));

                if (ret==-1)
                {
                    throw new TreeException_KeyExists();
                }

                if (ret==-2)
                {
                    throw new TreeException_ParentNotExists();
                }

                return ret==0;
            }
        }

        public bool UpdateCheck(TreeNodeForStorage item)
        {
            string sql = @"
                                    if not exists(select top 1 CategoryID from Kugar_Category where CategoryID='{0}' )
                                        begin
                                                select -1
                                        end 
                                    else
                                        if '{1}' <> ''
                                        begin
                                            if EXISTS(select top 1 [CategoryID] from Kugar_Category where [CategoryID]='{1}')
                                                begin
                                                        select -2
                                                 end 
                                        end
                                    
                                     select 0
                                ";

            using (var conn = _pool.Take())
            {
                int ret = 0;

                ret = conn.ExecuteScalar<int>(string.Format(sql, item.Key.ToStringEx(), item.ParentKey.ToStringEx()));

                if (ret == -1)
                {
                    throw new TreeException_KeyNotExists();
                }

                if (ret == -2)
                {
                    throw new TreeException_ParentNotExists();
                }

                return ret == 0;
            }
        }

        public bool DeleteCheck(TreeNodeForStorage item)
        {
            string sql = @"
                                    if not exists(select top 1 CategoryID from Kugar_Category where CategoryID='{0}' )
                                        begin
                                                select -1
                                        end 
                                     select 0
                                ";

            using (var conn = _pool.Take())
            {
                int ret = 0;

                ret = conn.ExecuteScalar<int>(string.Format(sql, item.Key.ToStringEx(), item.ParentKey.ToStringEx()));

                if (ret == -1)
                {
                    throw new TreeException_KeyNotExists();
                }

                return ret == 0;
            }
        }

        public bool Add(TreeNodeForStorage item)
        {
            string sql = @"
                                    set nocount on
                                    if EXISTS (select top 1 [CategoryID] from Kugar_Category where [CategoryID]=@key)
                                    begin
                                            select -1
                                    end 
                                    else 
                                    begin
                                            if EXISTS(select top 1 [CategoryID] from Kugar_Category where [CategoryID]=@parentKey)
                                                begin
                                                    insert into Kugar_Category ([ModuleID],[CategoryID],[ParentID],[CategoryText],[Path])
                                                    values(@moduleID,@key,@parentKey,@text,@path)
                                                    select 0
                                                end 
                                            else
                                                begin
                                                        select -2
                                                end
                                    end 
                                    set nocount OFF

                            ";

            using (var conn=_pool.Take())
            {
                var cmd =conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.AddParameter("@key",DbType.String,item.Key );
                cmd.AddParameter("@moduleID", DbType.String, _moduleID.ToStringEx());
                cmd.AddParameter("@parentKey", DbType.String, item.ParentKey.ToStringEx());
                cmd.AddParameter("@text",DbType.String, item.Text.ToStringEx());
                cmd.AddParameter("@path", DbType.String, item.Path.ToStringEx());

                var ret = conn.ExecuteScalar<int>(cmd);

                if (ret!=0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        public bool Update(TreeNodeForStorage item)
        {
            string sql = @"
                                    set nocount on
                                    if not exists(select top 1 CategoryID from Kugar_Category where CategoryID=@key )
                                    begin
                                            select -1
                                    end 
                                    else 
                                    begin
                                            if exists(select top 1 CategoryID from  Kugar_Category where CategoryID=@parentKey)
                                            begin
                                                update Kugar_Category set ParentID=@parentKey,CategoryText=@text,Path=@path
                                                where ModuleID=@moduleID and CategoryID=@key
                                                select 0
                                            end 
                                            else
                                            begin
                                                    select -2
                                            end
                                    end 
                                    set nocount off
                            ";

            using (var conn = _pool.Take())
            {
                var cmd = conn.CreateCommand();
                cmd.Transaction = conn.BeginTransaction();
                cmd.CommandText = sql;
                cmd.AddParameter("@key", DbType.String, item.Key.ToStringEx());
                cmd.AddParameter("@moduleID", DbType.String, _moduleID.ToStringEx());
                cmd.AddParameter("@parentKey", DbType.String, item.ParentKey.ToStringEx());
                cmd.AddParameter("@text", DbType.String, item.Text.ToStringEx());
                cmd.AddParameter("@path", DbType.String, item.Path.ToStringEx());

                int ret = 0;

                try
                {
                    ret = conn.ExecuteScalar<int>(cmd);

                    conn.CommitTransaction();
                }
                catch (Exception)
                {
                    conn.RollbackTransaction();
                }
                

                if (ret != 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        public bool Delete(string key)
        {
            string sql = @"
                                    set nocount on
                                    if not exists(select top 1 CategoryID from Kugar_Category where CategoryID=@key and ModuleID=@ModuleID)
                                    begin
                                            select -1
                                    end 
                                    else 
                                    begin
                                            declare @path varchar(500)
                                            select @path=path from Kugar_Category where CategoryID=@key and ModuleID=@ModuleID
                                            set @path=@path + '%'
                                            delete from Kugar_Category where CategoryID=@key and ModuleID=@ModuleID
                                            delete from Kugar_Category where path like @path and ModuleID=@ModuleID
                                            
                                            select 0
                                    end 
                                    set nocount off
                            ";

            using (var conn = _pool.Take())
            {
                
                var cmd = conn.CreateCommand();
                cmd.Transaction = conn.BeginTransaction();
                cmd.CommandText = sql;
                cmd.AddParameter("@key", DbType.String,key);
                cmd.AddParameter("@moduleID", DbType.String, _moduleID);

                int ret = 0;

                try
                {
                    ret = conn.ExecuteScalar<int>(cmd);

                    conn.CommitTransaction();
                }
                catch (Exception)
                {
                    conn.RollbackTransaction();
                }
                

                if (ret != 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        public void Save(Tree srcTree)
        {
            var nodeList = srcTree.GetAllChild(srcTree.Nodes[0]);

            using (var conn=_pool.Take())
            {
                try
                {
                    var tran=conn.BeginTransaction();

                    conn.ExecuteNoQuery("delete from Kugar_Category where ModuleID='" + _moduleID + "'");

                    if(nodeList!=null && nodeList.Count>0)
                    {
                        var cmd = conn.CreateCommand();

                        cmd.Transaction = tran;

                        cmd.CommandText = @"insert into Kugar_Category ([ModuleID],[CategoryID],[ParentID],[CategoryText],[Path])
                                                            values(@moduleID,@key,@parentKey,@text,@path)";

                        cmd.AddParameter("@moduleID", DbType.String, _moduleID);
                        cmd.AddParameter("@key", DbType.String, "");
                        cmd.AddParameter("@parentKey", DbType.String, "");
                        cmd.AddParameter("@text", DbType.String, "");
                        cmd.AddParameter("@path", DbType.String, "");

                        foreach (var node in nodeList)
                        {
                            cmd.Parameters["@key"].Value = node.Key;
                            cmd.Parameters["@parentKey"].Value = node.Parent.Key.ToStringEx();
                            cmd.Parameters["@text"].Value = node.Text.ToStringEx();
                            cmd.Parameters["@path"].Value = node.Path.ToStringEx();

                            cmd.ExecuteNonQuery();
                        }
                    }

                    conn.CommitTransaction();
                }
                catch (Exception)
                {
                    conn.RollbackTransaction();
                }

                


            }

        }

        public void LoadTree(Tree targetTree)
        {
            using (var conn=_pool.Take())
            {
                var dt=conn.ExecuteDataTable("select CategoryID,CategoryText,ParentID from Kugar_Category where ModuleID='" + _moduleID + "'");

                var dv = new DataView(dt);

                dv.RowFilter = "ParentID=''";

                foreach (DataRowView row in dv)
                {
                    targetTree.Nodes.Add(rowToNode(targetTree,row.Row));
                    dt.Rows.Remove(row.Row);
                }

                foreach (var node in targetTree)
                {
                    buildTree(node, dt);
                }

            }
        }

        private void buildTree(TreeNode parentNode,DataTable dt)
        {
            if (dt.Rows.Count<=0)
            {
                return;
            }

            var dv = new DataView(dt);

            dv.RowFilter = "ParentID='" + parentNode.Key + "'";

            if (dv.Count<=0)
            {
                return;
            }
            else
            {
                //List<DataRow> lst=new List<DataRow>();
                foreach (DataRowView row in dv)
                {
                    parentNode.Nodes.Add(rowToNode(parentNode.Tree, row.Row));
                    dt.Rows.Remove(row.Row);
                    //lst.Add(row.Row);
                }

                //if (lst.Count>0)
                //{
                //    //lst.ForEach((r)=>dt.Rows.Remove(r));
                //}

                foreach (var node in parentNode.Nodes)
                {
                    buildTree(node, dt);
                }
            }

        }



        public IList<TreeNodeForStorage> GetCategoryList(string moduleID)
        {
            var sql = @"select * from Kugar_Category where moduleID='{0}'
                                ";

            using (var conn=_pool.Take())
            {
                var dt=conn.ExecuteDataTable(string.Format(sql, moduleID));

                if (dt!=null && dt.Rows.Count>0)
                {
                    var lst = new List<TreeNodeForStorage>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        var row = dt.Rows[i];

                        lst.Add(rowToNodeStorage(row));
                    }

                    return lst;
                }
                else
                {
                    return null;
                }

                
            }
        }

        public IList<TreeNodeForStorage> GetChildCategoryByPath(string path)
        {
            using (var conn=_pool.Take())
            {
                var dt = conn.ExecuteDataTable("select * from Kugar_Category where path=='" + path + "%'");

                if (dt==null || dt.Rows.Count<=0)
                {
                    return null;
                }
                List<TreeNodeForStorage> lst=new List<TreeNodeForStorage>(dt.Rows.Count);
                foreach (DataRow row in dt.Rows)
                {
                    lst.Add(rowToNodeStorage(row));
                }

                return lst;
            }
        }

        public IList<TreeNodeForStorage> GetChildCategory(string parentKey)
        {
            var sqlStr = @"
                                    declare @path varchar(100)
                                    select @path=Path from Kugar_Category where key='{0}'
                                    select * from Kugar_Category where path like @path+'%'

                                ";

            using (var conn=_pool.Take())
            {
                var dt=conn.ExecuteDataTable(string.Format(sqlStr, parentKey));

                if (dt!=null && dt.Rows.Count<=0)
                {
                    List<TreeNodeForStorage> lst = new List<TreeNodeForStorage>(dt.Rows.Count);
                    foreach (DataRow row in dt.Rows)
                    {
                        lst.Add(rowToNodeStorage(row));
                    }

                    return lst;
                }
                else
                {
                    return null;
                }    
            }
        }

        private TreeNode rowToNode(Tree tree, DataRow row)
        {
            var node = tree.CreateNode();

            node.Text = row["CategoryText"].ToStringEx();
            node.Key = row["CategoryID"].ToStringEx();

            return node;
        }

        private TreeNodeForStorage rowToNodeStorage(DataRow row)
        {
            var ns = new TreeNodeForStorage();

            ns.Text = row["CategoryText"].ToStringEx();
            ns.Key = row["CategoryID"].ToStringEx();
            ns.ParentKey = row["ParentID"].ToStringEx();

            return ns;
        }
    }

}
